// sous module specfique de gestion des acces a la table PROPRIETAIRE
// Table : Proprietaire(idProprio, nom, prenom, adresse, tel)
package bdd;

import java.sql.*;
import java.util.*;
import beans.*;

public class DB_PROPRIETAIRE {

  Connection conn;
  PreparedStatement ps_select;
  PreparedStatement ps_insert;
  PreparedStatement ps_update;
  PreparedStatement ps_delete;

  public DB_PROPRIETAIRE(Connection conn) {
     this.conn=conn;
     try{
        ps_select = conn.prepareStatement("select nom,prenom,adresse,tel from proprietaire where idProprio=?");
  	ps_insert = conn.prepareStatement("insert into proprietaire values(?,?,?,?,?)");
         ps_update = conn.prepareStatement("update proprietaire set nom=?,  prenom=?, adresse=?, tel=? where idProprio=?");
         ps_delete = conn.prepareStatement("delete from proprietaire where idProprio=?"); 
     } catch(SQLException ex){System.out.println(ex);}
  }
      
  public Proprietaire getProprietaire(int idProprio) {
     Proprietaire p=null;
     try{
      ps_select.setInt(1,idProprio);
      ResultSet rs = ps_select.executeQuery();
      if(rs.next()){
	  String nom = rs.getString("nom");
	  String prenom = rs.getString("prenom");
	  String adresse = rs.getString("adresse");
	  String tel = rs.getString("tel");
          p = new Proprietaire(idProprio,nom,prenom,adresse,tel);        
	    }
     } catch(SQLException ex){ System.out.println(ex);}
     return p;
  }

  public void insertProprietaire(Proprietaire p){
		try{
			ps_insert.setInt(1,p.getIdProprio());
			ps_insert.setString(2,p.getNom());
			ps_insert.setString(3,p.getPrenom());
			ps_insert.setString(4,p.getAdresse());
			ps_insert.setString(5,p.getTel());
			ps_insert.executeUpdate();
     		} catch(SQLException ex){System.out.println(ex);}  
  }
        
  public void updateProprietaire(Proprietaire p){
		try{ 
			ps_update.setString(1,p.getNom());
			ps_update.setString(2,p.getPrenom());
			ps_update.setString(3,p.getAdresse());
			ps_update.setString(4,p.getTel());
			ps_update.setInt(5,p.getIdProprio());
			ps_update.executeUpdate();
		} catch(SQLException ex){System.out.println(ex);}     
  }

  public void deleteProprietaire(int idProprio){
        try{
		ps_delete.setInt(1,idProprio);
		ps_delete.executeUpdate();
     	      } catch(SQLException ex){System.out.println(ex);} 
  }

  public ArrayList<Proprietaire> getProprietaires(String req) {
	// pre-condition: req est de la forme "SELECT * FROM proprietaire ..."
	// car il s'agit d'extraire un ensemble de proprietaires

        Proprietaire p;
	ArrayList<Proprietaire> alp = null;

	try{ 
		alp = new ArrayList<Proprietaire>(); 
		Statement st = conn.createStatement(); 
		ResultSet rs = st.executeQuery(req); 
		while(rs.next()){ 
			p = new Proprietaire(rs.getInt("idProprio"), rs.getString("nom"), rs.getString("prenom"), rs.getString("adresse"), rs.getString("tel")); 
			alp.add(p); 
		} 
	} catch(SQLException ex){System.out.println(ex);} 
	return alp;
  }

  public ArrayList<Proprietaire> getProprietaires() {
	return getProprietaires("select * from proprietaire");
  }


}
